/**
 * 
 */
package com.dsbridge.bank.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.dsbridge.bank.model.DBUtils;
import com.dsbridge.bank.model.Transction;

/**
 * @author Naveen
 * 
 */
@Repository
public class TransactionDAO {

	public int insert(Transction t) throws SQLException {
		int result = 0;
		PreparedStatement ps = null;
		String sql = "INSERT INTO TRANSACTION VALUES(?,?,?)";
		Connection con = DBUtils.getConnection();

		if (con != null) {

			ps = (PreparedStatement) con.prepareStatement(sql,
					Statement.RETURN_GENERATED_KEYS);

			ps.setInt(1, t.getAccountid());
			ps.setString(2, t.getTransactiontype());
			ps.setDouble(3, t.getAmount());

			result = ps.executeUpdate();

		} else {
			System.out.println("Unable to get the connection");
		}

		return result;

	}

	public int update(Transction t) throws SQLException {
		int result = 0;
		PreparedStatement ps = null;
		String sql = "UPDATE TRANSACTION SET ACCOUNT_NUM = ? WHERE AMOUNT = ?";
		Connection con = DBUtils.getConnection();

		if (con != null) {
			ps = (PreparedStatement) con.prepareStatement(sql);

			// Populate the data

			ps.setInt(1, t.getAccountid());
			ps.setDouble(2, t.getAmount());
			// Execute the statements
			result = ps.executeUpdate();

		} else {
			System.out.println("Unable to get the connection");
		}
		// close the statements and connections
		ps.close();
		DBUtils.closeConnection();

		return result;

	}

	public int delete(Transction t) throws SQLException {
		int result = 0;
		PreparedStatement ps = null;
		String sql = "DELETE FROM TRANSACTION WHERE ACCOUNT_NUM = ?";
		Connection con = DBUtils.getConnection();

		if (con != null) {
			ps = (PreparedStatement) con.prepareStatement(sql);

			// Populate the data

			ps.setInt(1, t.getAccountid());

			// Execute the statements
			result = ps.executeUpdate();

		} else {
			System.out.println("Unable to get the connection");
		}
		// close the statements and connections
		ps.close();
		DBUtils.closeConnection();

		return result;

	}

	public static List<Transction> retrieveTransbyAccountid(int accountid)
			throws SQLException {

		List<Transction> list = new ArrayList<Transction>();

		PreparedStatement ps = null;
		String sql = "SELECT * FROM TRANSACTION WHERE ACCOUNT_NUM = ? ";
		Connection con = DBUtils.getConnection();
		if (con != null) {
			ps = (PreparedStatement) con.prepareStatement(sql);
			// populate the data
			ps.setInt(1, accountid);
			// Execute the statements by passing the customerid
			ResultSet result = ps.executeQuery();

			while (result.next()) {
				Transction transactions = new Transction();

				transactions.setTaxid(result.getInt("TAX_ID"));
				transactions.setAccountid(result.getInt("ACCOUNT_NUM"));

				transactions.setTransactiontype(result
						.getString("TRANSACTION_TYPE"));
				transactions.setAmount(result.getDouble("AMOUNT"));

				list.add(transactions);

			}
		} else {
			System.out.println("Unable to get the connection");
		}
		// close the statements and connections
		ps.close();
		DBUtils.closeConnection();
		return list;

	}

}
